﻿using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace TSites.Offices
{
	public class ExcelObject
	{
		#region Fields

		private string _defaultConnString = "Provider=Microsoft.{0}.OLEDB.{1};Data Source={2};Extended Properties=\"Excel {3};HDR=YES;IMEX=1\"";
		private string _filePath;
		
		#endregion

		#region Ctor

		public ExcelObject(string filePath)
		{
			this._filePath = filePath;
		}
		
		#endregion

		#region Private methods

		private string ConnectionString
		{
			get
			{
				string connString;
				if (Path.GetExtension(_filePath).Equals(".xls")) // Normal
				{
					connString = string.Format(_defaultConnString, "Jet", "4.0", _filePath, "8.0");
				}
				else // Office 2007 ConnectionString
				{
					connString = string.Format(_defaultConnString, "Ace", "12.0", _filePath, "12.0");
				}

				return connString;
			}
		}

		private OleDbConnection Connection
		{
			get
			{
				OleDbConnection oleDbCon = new OleDbConnection(ConnectionString);
				oleDbCon.Open();
				return oleDbCon;
			}
		}

		private DataTable GetSchema()
		{
			DataTable dtSchema = null;
			using (OleDbConnection oldDb = Connection)
			{
				dtSchema = oldDb.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
			}

			return dtSchema;
		}

		#endregion

		public List<string> GetWorkSheets()
		{
			DataTable dtResult = GetSchema();

			string name;
			List<string> workbookNames = new List<string>();
			foreach (DataRow dr in dtResult.Rows)
			{
				name = dr["TABLE_NAME"].ToString();
				if (name.EndsWith("$"))
				{
					workbookNames.Add(name);
				}
			}

			return workbookNames;
		}

		public DataTable ReadTable(string tableName, string criteria = null)
		{
			DataTable resultTable = null;
			using (OleDbConnection oldDb = Connection)
			{
				string cmdText = "SELECT * FROM [{0}]";
				if (!string.IsNullOrWhiteSpace(criteria))
				{
					cmdText += " WHERE " + criteria;
				}

				OleDbCommand cmd = new OleDbCommand(string.Format(cmdText, tableName), oldDb);
				OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);

				DataSet ds = new DataSet();
				adpt.Fill(ds, tableName);
				if (ds.Tables.Count > 0)
				{
					resultTable = ds.Tables[0];
				}
			}

			return resultTable;
		}
	}
}
